
import org.junit.jupiter.api.Test;
import java.sql.Date;
import java.util.UUID;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;

public class Persondemo {
    @Test
    public void insertAperson() throws SQLException {
        Connection connection = null;
        try {
    
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://210.30.107.239/person?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url,"root","123456");
    
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO aperson VALUE(?,?,?,?) ");

            Date dt = new Date(System.currentTimeMillis());
            preparedStatement.setString(1, UUID.randomUUID().toString());
            preparedStatement.setString(2,"张三");
            preparedStatement.setInt(3,22); 
            preparedStatement.setDate(4,dt);
            int res = preparedStatement.executeUpdate();
            System.out.println("insertseccess: " + res);


        }
	 catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
	finally {
            connection.close();
        }
    }

    @Test
    public void deleteAperson() throws SQLException {
        Connection connection = null;
        try {
       
            Class.forName("com.mysql.cj.jdbc.Driver");
       
            String url = "jdbc:mysql://210.30.107.239/person?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url,"root","123456");
        
            PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM aperson WHERE name LIKE ?");
      
      
            preparedStatement.setString(1,"%张三%");
            int res = preparedStatement.executeUpdate();
            System.out.println("deletesuccess " + res);
        

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
	finally {
         
            connection.close();
        }
    }

@Test
    public void updateAperson() throws SQLException {
        Connection connection = null;
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
   
            String url = "jdbc:mysql://210.30.107.239/person?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url,"root","123456");
      
            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE aperson SET age = 666 WHERE name like ?");
       
            Date dt = new Date(System.currentTimeMillis());
            preparedStatement.setString(1,"%李四%");//
            int res = preparedStatement.executeUpdate();//执行insert update delete 统一用executeUpdate
            System.out.println("modifysuccess: " + res);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
	finally {
     
            connection.close();      
        }
    }
}


    @Test
    public void queryAperson() throws SQLException {
        Connection connection = null;
        try {
      
            Class.forName("com.mysql.cj.jdbc.Driver");
     
            String url = "jdbc:mysql://210.30.107.239/person?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            connection = DriverManager.getConnection(url,"root","123456");
        
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM aperson WHERE name LIKE ?");
       
            preparedStatement.setString(1,"%张三%");
            ResultSet rs = preparedStatement.executeQuery();
        
            List<Person> list = new ArrayList<>();
            System.out.println("========================================================");
            while(rs.next()){
                String id = rs.getString(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                Date dt = rs.getDate(4);
                System.out.println(id + ":" + name + ":" + age + ":" + dt.toString());

                Person pp = new Person();
                pp.setId(id);
                pp.setName(name);
                pp.setAge(age);
                pp.setBirthday(dt);
                list.add(pp);
            }
            System.out.println("——————————————————————————————————————————————");
            System.out.println(list.toString());

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
	finally 
{
            connection.close();
        }
    }
    

